﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Collections.Generic;
using System.Reflection;
using System.IO;

using DevExpress.Utils;
using DevExpress.Utils.Menu;
using DevExpress.XtraGrid.Menu;
using DevExpress.XtraGrid.Columns;
using Microsoft.Office.Core;
using System.Globalization;
using System.Runtime.InteropServices;
using System.Reflection;

using DevExpress.XtraGrid.Views.Grid;

using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Base;

namespace NNLInterface
{
    public partial class frmorderAnalysis : NNLInterface.frmBaseToolXC
    {

        private DataTable dtFCData;
        private DataTable dtFCGS;
        private DataTable dtMoGS;
        private DataTable dtMoGSLast;
        private DataTable dtNotComMo;
        private DataTable dtOrderGS;
        bool WriteDataFlag = false;
        private string endDate;
        private string startDate;
        Excel.Application xApp;

        string fileterFcCustNoSer = "";
        string FcCustNoSer = common.sysParameter.FcCustNoSer;


        string filterCustPQSer;
        string CustPQSer = common.sysParameter.CustPQSer;

        string filterCustMoSer;
        string CustMoSer = common.sysParameter.FcCustNoMo;

        
        DataTable dtFCGSH1 = new DataTable();

        public frmorderAnalysis()
        {
            InitializeComponent();
            this.SetToolVisible(common.ToolVisible.ImportData);
            this.logButtonVisibility = false;
            this.ImportButtonVisibility = false;
            this.CmdSearch.ImageIndex = 0;
            this.CmdSearch.Caption = "报表生成";
        }


        private void frmCustRpt_ToolButtonClick(object sender, string e)
        {
            Cursor currentCursor = Cursor.Current;
            try
            {
                switch (e)
                {
                    case "search":
                        if (!CheckData()) return;
                        
                           if(optGroup.SelectedIndex==0)
                           {
                               if (!CreateQYReport()) return ;
                           }
                           else
                           {
                               if (!CreateDepReport()) return;
                           }
                        break;


                    case "exit":
                        {
                            this.Dispose();
                            break;
                        }


                    default:
                        {
                            break;
                        }
                }


            }
            catch (Exception ex)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message, common.sysParameter.MessageInf, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                this.barTip.Caption = "";
            }
        }


        //生成部门报表
        private bool CreateDepReport()
        {
            try
            {
                xApp = new Excel.ApplicationClass();
                Cursor currentCursor = Cursor.Current;

                //模板文件名称
                string sourceFileName = common.GetSolutionPath(Application.StartupPath) + @"orderAnalysis.xls";
                currentCursor = Cursor.Current;
                startDate = dteFrom.Text;
                endDate = dteTo.Text;


                //新建EXCEL文档
                string strFileName = common.ShowSaveFileDialog("保存Excel文件", "Microsoft Excel(*.xls)|*.xls");

                if (strFileName == "") return false;

                //取得客户的选择系列
                GetCustSer();

                string curMonthDataT = CreateOrderGS();

                //显示数据
                ShowListData();

                //取得销货数据(取出最近一月有销货的-当月前两周的销货数据，客户不在forecast列表中的销货数据)

                //取得有料订单工时取erp中的MO数据，且物料最终交期为当月，并且小于或等于当前日期的Mo数据

                if (strFileName == "")
                {
                    Application.DoEvents();
                    return false;
                }

                Cursor.Current = Cursors.WaitCursor;
                this.barTip.Caption = "新建Excel文档...";
                Application.DoEvents();
                Cursor.Current = Cursors.WaitCursor;

                File.Copy(sourceFileName, strFileName, true);

                xApp.Workbooks.Open(strFileName, Missing.Value, Missing.Value,
                      Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                      Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                      Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                xApp.Visible = false;
                xApp.DisplayAlerts = false;
                xApp.Calculation = (Excel.XlCalculation)Excel.Constants.xlManual;
                Excel.Workbook xBook = xApp.Workbooks[1];
                Excel.Worksheet sheet = (Excel.Worksheet)xApp.ActiveSheet;// 获取当前工作表
                xBook = xApp.Workbooks[1];

                //按循环写入每个月的数据
                DateTime dteTmp = Convert.ToDateTime(this.dteFrom.Text + "-01");
                DateTime dteStartDate = Convert.ToDateTime(this.dteFrom.Text + "-01");
                DateTime dteEndDate = Convert.ToDateTime(this.dteTo.Text + "-01");

                Cursor.Current = Cursors.WaitCursor;
                this.barTip.Caption = "处理模板行...";
                Application.DoEvents();
                Cursor.Current = Cursors.WaitCursor;

                //复制月份数据
                int monthCount = (int)Microsoft.VisualBasic.DateAndTime.DateDiff(Microsoft.VisualBasic.DateInterval.Month, dteStartDate, dteEndDate) + 1;
                if (monthCount > 2)
                    InsertBlankRow(monthCount - 2, 4, xApp, sheet);


                //写入表头数据
                string strTitle = Convert.ToDateTime(dteFrom.Text + "-01").AddMonths(-1).Month.ToString();
                sheet.Cells[3, 4] = "无FC客户按" + strTitle + "月份实际订单工时计算";

                //写入每个月的数据
                for (int i = 0; i < monthCount; i++)
                {
                    //得到月份
                    string strMonthNo = dteTmp.ToString("yyyy-MM", System.Globalization.DateTimeFormatInfo.InvariantInfo);

                    //写入每个月的月份

                    sheet.Cells[4 + i * 2, 1] = dteTmp.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo);

                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "现有订单工时状态（H）";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;
                    string strLast = "";
                    if (i == 0)
                    {
                        strLast = "left(dbo.GetShortDate(RDate),7)<='" + strMonthNo + "'";
                    }
                    else
                        strLast = "left(dbo.GetShortDate(RDate),7)='" + strMonthNo + "'";

                    string sql = "SELECT  \n"
                               + " isnull(sum(qty*cb.Zgs),0)/3600 AS GS  \n"
                               + " FROM vie_ErpNotCompleteMoorderAnalysis aa  \n"
                               + "LEFT OUTER JOIN CapabilityBas cb  \n"
                               + "ON aa.ItemCode=cb.ItemCode COLLATE Chinese_PRC_CI_AS  \n"
                               + "WHERE " + strLast;
                    DataTable dtmoGSnew = accessdb.GetRecord(sql).ADODataRst;


                    sheet.Cells[4 + i * 2, 2] = Convert.ToDecimal(dtmoGSnew.Rows[0]["gs"]);



                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "FC工时（H）";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;



                    //写入FC工时（H）
                    if (i == 0)
                    {
                        if (WriteDataFlag)
                            sheet.Cells[4 + i * 2, 3] = Convert.ToDecimal(dtFCGSH1.Rows[0]["gs"]);
                        else if (this.dteFrom.Text == curMonthDataT)
                        {
                            sheet.Cells[4 + i * 2, 3] = 0;
                        }
                        else
                        {
                            DataRow[] drFCGS = dtFCGS.Select("MonthNo='" + strMonthNo + "'");
                            if (drFCGS.Length >= 1)
                            {

                                sheet.Cells[4 + i * 2, 3] = Convert.ToDecimal(drFCGS[0]["GS"]);
                            }
                        }

                    }
                    else
                    {
                        DataRow[] drFCGS = dtFCGS.Select("MonthNo='" + strMonthNo + "'");
                        if (drFCGS.Length >= 1)
                        {

                            sheet.Cells[4 + i * 2, 3] = Convert.ToDecimal(drFCGS[0]["GS"]);
                        }
                    }

                    //写入无FC客户按8月份实际订单工时计算
                    //(取出上一月的有销货数据-当月前两周的销货数据，通过料号关联BOM数据所在的部门。客户不在提供有forecast的列表中的所有MO数据)
                    string strLastMonthNo = dteTmp.AddMonths(-1).ToString("yyyy-MM", System.Globalization.DateTimeFormatInfo.InvariantInfo);
                    decimal lastMonthGSDG = 0;
                    decimal curMonthGSDG = 0;

                    decimal lastMonthGSGX = 0;
                    decimal curMonthGSGX = 0;

                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "无FC客户按8月份实际订单工时计算(DG)\n(取上月DG)";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;



                    string strSQL = "SELECT isnull(sum(qty*isnull(cb.ZGS,0))/3600,0) GS FROM vie_ErpSaleData aa \n"
                           + "LEFT OUTER JOIN CapabilityBas cb \n"
                           + "ON aa.PRD_NO=cb.ItemCode  COLLATE Chinese_PRC_CI_AS \n"
                           + "WHERE "
                           + "   LEFT(dbo.GetShortDate(aa.ps_dd),7)='" + strLastMonthNo + "' \n"
                           + " and   LEFT(dbo.GetShortDate(aa.OS_DD),7)='" + strLastMonthNo + "' \n"
                           + " AND isnull(aa.DEP,'') Not LIKE '%GX%' \n"
                           + " AND aa.SUP_PRD_NO NOT IN (" + fileterFcCustNoSer + ")";
                    dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                    if (dtOrderGS.Rows.Count >= 1)
                    {
                        curMonthGSDG = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]);
                        //写入数据(DG,第15列)
                        sheet.Cells[4 + i * 2, 15] = curMonthGSDG;
                    }





                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "无FC客户按8月份实际订单工时计算(GX)(取当月GX)";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;

                    //取当月份的数据(DG)
                    //strSQL = "SELECT isnull(sum(qty*isnull(cb.ZGS,0))/3600,0) GS FROM vie_ErpSaleData aa \n"
                    //        + "LEFT OUTER JOIN CapabilityBas cb \n"
                    //        + "ON aa.PRD_NO=cb.ItemCode  COLLATE Chinese_PRC_CI_AS\n"
                    //        + "WHERE dbo.GetShortDate(aa.ps_dd)<='" + strMonthNo + "-15' \n"
                    //        + " and   LEFT(dbo.GetShortDate(aa.ps_dd),7)='" + strMonthNo + "' \n"
                    //        + " and   LEFT(dbo.GetShortDate(aa.OS_DD),7)='" + strMonthNo + "' \n"
                    //        + "AND isnull(aa.DEP,'')  LIKE '%GX%' \n"
                    //        + "AND aa.SUP_PRD_NO NOT IN (" + fileterFcCustNoSer + ")";


                    strSQL = "SELECT isnull(sum(qty*isnull(cb.ZGS,0))/3600,0) GS FROM vie_ErpSaleData aa \n"
                            + "LEFT OUTER JOIN CapabilityBas cb \n"
                            + "ON aa.PRD_NO=cb.ItemCode  COLLATE Chinese_PRC_CI_AS\n"
                            + "WHERE  LEFT(dbo.GetShortDate(aa.ps_dd),7)='" + strLastMonthNo + "' \n"
                            + " and   LEFT(dbo.GetShortDate(aa.OS_DD),7)='" + strLastMonthNo + "' \n"
                            + "AND isnull(aa.DEP,'')  LIKE '%GX%' \n"
                            + "AND aa.SUP_PRD_NO NOT IN (" + fileterFcCustNoSer + ")";
                    dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                    if (dtOrderGS.Rows.Count >= 1)
                    {
                        curMonthGSGX = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]);
                        //写入数据(DG,第15列)
                        sheet.Cells[5 + i * 2, 15] = curMonthGSGX;
                    }



                    //写入合计（直接按模板公式)

                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "写入有料订单工时(DG)";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;

                    //写入有料订单工时(东莞)(取erp中的MO数据，且物料最终交期为小于或等于当月的25日的MO数据)
                    string strAdd = "";
                    if (i == 0)
                        strAdd = " and  left(dbo.GetShortDate(Rdate),7)<='" + strMonthNo + "'";
                    else
                        strAdd = " and  left(dbo.GetShortDate(Rdate),7)='" + strMonthNo + "'";

                    strSQL = "SELECT \n"
                           + " isnull(sum(qty*isnull(cb.ZGS,0))/3600,0) AS GS \n"
                           + " FROM vie_ErpNotCompleteMoorderAnalysis aa \n"
                           + "LEFT OUTER JOIN CapabilityBas cb \n"
                           + "ON aa.ItemCode=cb.ItemCode COLLATE Chinese_PRC_CI_AS \n"
                           + "WHERE dbo.GetShortDate(MrpDate)<='" + strMonthNo + "-25' \n"
                           + strAdd
                           + "  AND isnull(dep,'') NOT LIKE '%GX%' \n";

                    dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                    if (dtOrderGS.Rows.Count >= 1)
                        sheet.Cells[4 + i * 2, 10] = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]);
                    //写入有料订单工时(广西)(取erp中的MO数据，且物料最终交期为小于或等于当月的25日的MO数据)

                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "写入有料订单工时(GX)";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;
                    strSQL = "SELECT \n"
                            + " isnull(sum(qty*isnull(cb.zgs,0))/3600,0) AS GS \n"
                            + " FROM vie_ErpNotCompleteMoorderAnalysis aa \n"
                            + "LEFT OUTER JOIN CapabilityBas cb \n"
                            + "ON aa.ItemCode=cb.ItemCode COLLATE Chinese_PRC_CI_AS \n"
                            + "WHERE   dbo.GetShortDate(MrpDate)<='" + strMonthNo + "-25' "
                            + strAdd
                            + " AND isnull(dep,'')  LIKE '%GX%' \n";
                    dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                    if (dtOrderGS.Rows.Count >= 1)
                        sheet.Cells[5 + i * 2, 10] = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]);


                    //写入大客户走排期数据
                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "写入取当月的客户排期中存在的客户的Mo的数据(DG)";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;
                    string strFilterCur = "";
                    decimal balanceQty = 0;
                    //如果是第一个月，则需要加上当月之前的数据
                    if (i == 0)
                    {
                        strSQL = "SELECT  \n"
                      + "isnull(sum(qty*isnull(cb.ZGS,0))/3600,0) AS GS \n"
                      + " FROM vie_ErpNotCompleteMoorderAnalysis aa \n"
                      + "LEFT OUTER JOIN CapabilityBas cb \n"
                      + "ON aa.ItemCode=cb.ItemCode COLLATE Chinese_PRC_CI_AS \n"
                      + "WHERE LEFT( dbo.GetShortDate(RDate),7)<'" + strMonthNo + "' \n"
                      + " and CUS_NO IN (" + filterCustPQSer + ") \n"
                      + " AND isnull(dep,'') NOT LIKE '%GX%' \n";
                        dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                        if (dtOrderGS.Rows.Count >= 1)
                        {
                            balanceQty = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]);
                        }

                    }

                    //取当月的客户排期中存在的客户的Mo的数据
                    strSQL = "SELECT LEFT( dbo.GetShortDate(RDate),7) AS MonthNo, \n"
                       + "isnull(sum(qty*isnull(cb.ZGS,0))/3600,0) AS GS \n"
                       + " FROM vie_ErpNotCompleteMoorderAnalysis aa \n"
                       + "LEFT OUTER JOIN CapabilityBas cb \n"
                       + "ON aa.ItemCode=cb.ItemCode COLLATE Chinese_PRC_CI_AS \n"
                       + "WHERE LEFT( dbo.GetShortDate(RDate),7)='" + strMonthNo + "' \n"
                       + " and CUS_NO IN (" + filterCustPQSer + ") \n"
                       + " AND isnull(dep,'') NOT LIKE '%GX%' \n"
                       + " group by  LEFT( dbo.GetShortDate(RDate),7)";

                    dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                    if (dtOrderGS.Rows.Count >= 1)
                        sheet.Cells[4 + i * 2, 12] = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]) + balanceQty;


                    balanceQty = 0;
                    //如果是第一个月，则需要加上当月之前的数据
                    if (i == 0)
                    {
                        strSQL = "SELECT LEFT( dbo.GetShortDate(RDate),7) AS MonthNo, \n"
                          + "isnull(sum(qty*isnull(cb.ZGS,0))/3600,0) AS GS \n"
                          + " FROM vie_ErpNotCompleteMoorderAnalysis aa \n"
                          + "LEFT OUTER JOIN CapabilityBas cb \n"
                          + "ON aa.ItemCode=cb.ItemCode COLLATE Chinese_PRC_CI_AS \n"
                          + "WHERE LEFT( dbo.GetShortDate(RDate),7)<'" + strMonthNo + "' \n"
                          + " and CUS_NO IN (" + filterCustPQSer + ") \n"
                          + " AND isnull(dep,'')  LIKE '%GX%' \n"
                          + " group by  LEFT( dbo.GetShortDate(RDate),7)";
                        dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                        if (dtOrderGS.Rows.Count >= 1)
                        {
                            balanceQty = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]);
                        }

                    }

                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "写入取当月的客户排期中存在的客户的Mo的数据(GX)";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;
                    strSQL = "SELECT LEFT( dbo.GetShortDate(RDate),7) AS MonthNo, \n"
                          + "isnull(sum(qty*isnull(cb.ZGS,0))/3600,0) AS GS \n"
                          + " FROM vie_ErpNotCompleteMoorderAnalysis aa \n"
                          + "LEFT OUTER JOIN CapabilityBas cb \n"
                          + "ON aa.ItemCode=cb.ItemCode COLLATE Chinese_PRC_CI_AS \n"
                          + "WHERE LEFT( dbo.GetShortDate(RDate),7)='" + strMonthNo + "' \n"
                          + " and CUS_NO IN (" + filterCustPQSer + ") \n"
                          + " AND isnull(dep,'')  LIKE '%GX%' \n"
                          + " group by  LEFT( dbo.GetShortDate(RDate),7)";
                    dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                    if (dtOrderGS.Rows.Count >= 1)
                        sheet.Cells[5 + i * 2, 12] = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]) + balanceQty;


                    //写入本月生产正式单工时(本月的DG的Mo工时合计)
                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "本月生产正式单工时(本月的DG的Mo工时合计)(DG)";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;

                    //本月生产正式单工时(本月的DG的Mo工时合计)

                    if (i == 0)
                    {
                        strAdd = "WHERE left(dbo.GetShortDate(RDate),7)<='" + strMonthNo + "'";
                    }
                    else
                        strAdd = "WHERE left(dbo.GetShortDate(RDate),7)='" + strMonthNo + "'";
                    strSQL = "SELECT \n"
                           + " isnull(sum(qty*cb.Zgs)/3600,0) AS GS \n"
                           + " FROM vie_ErpNotCompleteMoorderAnalysis aa \n"
                           + "LEFT OUTER JOIN CapabilityBas cb \n"
                           + "ON aa.ItemCode=cb.ItemCode COLLATE Chinese_PRC_CI_AS \n"
                           + strAdd
                           + "  AND isnull(dep,'') NOT LIKE '%GX%' \n";

                    dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                    if (dtOrderGS.Rows.Count >= 1)
                        sheet.Cells[4 + i * 2, 13] = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]);

                    //写入当月FC的数据（14列)
                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "当月FC的数据(DG)";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;

                    strSQL = "SELECT  \n"
                           + " isnull(sum(qty*Zgs)/3600,0) GS \n"
                           + "  FROM vie_ForeCastDataorderAnalysis aa \n"
                           + "  WHERE   dep NOT LIKE '%GX%' AND  \n"
                           + " dep NOT LIKE '%NLP%'   "
                           + "AND  LEFT(dbo.getshortdate(aa.CompleteDate),7)='" + strMonthNo + "' \n";
                    dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                    if (dtOrderGS.Rows.Count >= 1)
                        sheet.Cells[4 + i * 2, 14] = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]);


                    dteTmp = dteTmp.AddMonths(1);
                }

                //写入表头
                //sheet.Cells[1, 1] = strReportTilte;

                xApp.ScreenUpdating = true;
                xApp.Calculation = (Excel.XlCalculation)Excel.Constants.xlAutomatic;
                xBook.Save();
                xApp.Quit(); // 退出 Excel
                KillExcelProcess.Kill(xApp);//调用kill当前excel进程 
                xApp = null;
                if (DevExpress.XtraEditors.XtraMessageBox.Show("Excel文件已成功生成,现在打开文件吗(Y/N)?\n" + "文件信息:" + strFileName, common.sysParameter.MessageInf, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No) return false;

                //打开文件
                if (System.IO.File.Exists(strFileName))
                {
                    System.Diagnostics.Process process = new System.Diagnostics.Process();
                    process.StartInfo.FileName = strFileName;
                    process.StartInfo.Verb = "Open";
                    process.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal;
                    process.Start();
                }
                return true;
            }
            catch (Exception ex)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message, common.sysParameter.MessageInf, MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
            finally
            {
                if (xApp != null)
                {
                    KillExcelProcess.Kill(xApp);  //调用kill当前excel进程 
                }
            }
         

        }

        private string CreateOrderGS()
        {
            dtFCGS = new DataTable();
            dtFCGSH1 = new DataTable();
            WriteDataFlag = false;

            //如果所选择的日期在当月，并且当前的日期小于给定的值，则要用FC扣减Mo的数据，否则，取当月的数据
            int calfcday = common.sysParameter.CalFCmoDay;
            string curMonthDataT = System.DateTime.Now.ToString("yyyy-MM", System.Globalization.DateTimeFormatInfo.InvariantInfo);

            if (this.dteFrom.Text == curMonthDataT)
            {
                if (System.DateTime.Now.Day < calfcday)
                {

                    WriteDataFlag = true;
                    //按月分别使用在客户列表中存在的FC数据去扣减Mo
                    //取得FC数据，按月份，品目分组合计，并且客户序列存在于FC客户列表中的数据
                    string strFCData = "SELECT  vfcda.ItemCode,SUM(vfcda.Qty) SumQty,\n"
                                       + " LEFT(dbo.GetShortDate(vfcda.CompleteDate),7) \n"
                                       + "AS monthno,SUM(vfcda.Qty) balanceQty \n"
                                       + "FROM vie_ForeCastDataorderAnalysis AS vfcda \n"
                                       + "WHERE vfcda.CustNo IN (" + fileterFcCustNoSer + ") "
                                       + " AND vfcda.CompleteDate IS NOT NULL \n"
                                       + "AND LEFT(dbo.GetShortDate(vfcda.CompleteDate),7)='" + curMonthDataT + "' \n"
                                       + "GROUP BY vfcda.ItemCode,LEFT(dbo.GetShortDate(vfcda.CompleteDate),7)\n"
                                       + "order by ItemCode,LEFT(dbo.GetShortDate(vfcda.CompleteDate),7)";
                    dtFCGS = accessdb.GetRecord(strFCData).ADODataRst;


                    //MO数据
                    strFCData = "SELECT aa.ItemCode,SUM(aa.Qty) SumQty \n"
                               + "  FROM vie_ErpNotCompleteMoorderAnalysis aa \n"
                               + "  WHERE aa.CUS_NO IN (" + fileterFcCustNoSer + ") \n "
                               + "  and aa.cus_no in (" + filterCustMoSer + ") and "
                               + "  aa.RDate IS NOT NULL \n"
                               + "  GROUP BY aa.ItemCode,LEFT(dbo.GetShortDate(aa.RDate),7)";
                    DataTable dtMoGSf = accessdb.GetRecord(strFCData).ADODataRst;


                    //得到所有的FC的品目数据
                    DataTable dtItemCode = dtFCGS.DefaultView.ToTable(true, new string[] { "ItemCode" });

                    for (int i = 0; i < dtItemCode.Rows.Count; i++)
                    {
                        //得到此品目的Mo数据
                        string strItemCode = dtItemCode.Rows[i]["ItemCode"].ToString();

                        //得到此品目数据的MO数据
                        DataRow[] drMo = dtMoGSf.Select("ItemCode='" + strItemCode + "'");
                        if (drMo.Length >= 1)
                        {
                            decimal moQty = Convert.ToDecimal(drMo[0]["SumQty"]);

                            //按月循环各月的FC数据
                            DataRow[] drFC = dtFCGS.Select("itemcode='" + strItemCode + "'");

                            for (int jjj = 0; jjj < drFC.Length; jjj++)
                            {
                                decimal fcQty = Convert.ToDecimal(drFC[jjj]["SumQty"]);

                                //如果FC多，则直接将mo的数量置为0
                                if (moQty > 0)
                                {
                                    if (fcQty >= moQty)
                                    {
                                        drFC[jjj]["balanceQty"] = fcQty - moQty;
                                        moQty = 0;
                                    }
                                    else
                                    {
                                        drFC[jjj]["balanceQty"] = 0;
                                        moQty -= Convert.ToDecimal(drFC[jjj]["balanceQty"]);
                                    }
                                }
                            }

                        }
                    }

                    StringBuilder strFCGS = new StringBuilder();
                    //写入到FCGS数据表中
                    for (int i = 0; i < dtFCGS.Rows.Count; i++)
                    {
                        strFCGS.Append("INSERT INTO FCGS \n"
                                       + "( \n"
                                       + "	ItemCode, \n"
                                       + "	Qty, \n"
                                       + "	MonthNo, \n"
                                       + "	EntDate \n"
                                       + ") \n"
                                       + "VALUES('"
                                       + dtFCGS.Rows[i]["itemcode"].ToString() + "',"
                                       + dtFCGS.Rows[i]["balanceqty"].ToString() + ",'"
                                       + dtFCGS.Rows[i]["monthno"].ToString() + "'"
                                       + ",getdate())\n");
                    }


                    string strInsertData = strFCGS.ToString();
                    strInsertData = " SET XACT_ABORT ON    BEGIN TRAN\n  truncate table fcgs \n" + strInsertData + " commit tran";


                    accessdb.RsChange(strInsertData);

                    //得到每个月的在手订单的FC的工时数据
                    strInsertData = "   SELECT sum(f.Qty*isnull(cb.ZGS,0))/3600 gs,f.MonthNo FROM FCGS AS f \n"
                                   + "    LEFT OUTER JOIN CapabilityBas AS cb \n"
                                   + "    ON f.ItemCode=cb.ItemCode \n"
                                   + "    GROUP BY f.MonthNo";

                    dtFCGSH1 = accessdb.GetRecord(strInsertData).ADODataRst;


                }
            }
            return curMonthDataT;
        }


        private void ShowListData()
        {
            //显示MO数据和FC数据
            Cursor.Current = Cursors.WaitCursor;
            this.barTip.Caption = "进度:正在读取并显示FC数据...";
            Application.DoEvents();
            Cursor.Current = Cursors.WaitCursor;
            string strSQL = "select * from vie_FCdataYXNXAnalysis";
            dtFCData = accessdb.GetRecord(strSQL).ADODataRst;
            common.SubShowList(dtFCData, this.ListGridFc, this.ListViewFc, false);
            common.subIniGrid(this.ListViewFc, common.enumGridStyle.ViewStyle);
            common.GetResourceString(this.Name, this.ListViewFc);
            this.ListGridFc.MenuManager = this.barmanager;
            this.ListViewFc.BestFitColumns();

            Cursor.Current = Cursors.WaitCursor;
            this.barTip.Caption = "进度:正在读取并显示未完工的MO数据...";
            Application.DoEvents();
            Cursor.Current = Cursors.WaitCursor;
            strSQL = "select * from vie_ErpNotCompleteMoorderAnalysis";
            dtNotComMo = accessdb.GetRecord(strSQL).ADODataRst;
            common.SubShowList(dtNotComMo, this.ListGridMo, this.ListViewMo, false);
            common.subIniGrid(this.ListViewMo, common.enumGridStyle.ViewStyle);
            common.GetResourceString(this.Name, this.ListViewMo);
            this.ListGridMo.MenuManager = this.barmanager;
            this.ListViewMo.BestFitColumns();



            Cursor.Current = Cursors.WaitCursor;
            this.barTip.Caption = "进度:生成FC的数据...";
            Application.DoEvents();
            Cursor.Current = Cursors.WaitCursor;


            Cursor.Current = Cursors.WaitCursor;
            this.barTip.Caption = "进度:正在取得FC的每个月工时数据...";
            Application.DoEvents();
            Cursor.Current = Cursors.WaitCursor;

            //取得FC的工时数据(每个月的FC数据), 对于第一个月，取当月的forecast工时-当月之前(含当月)Mo的工时，以后月份的
            //直接按FC，还需要中上条件在FC客户列表中的客户才取
            strSQL = "SELECT left(dbo.GetShortDate(aa.CompleteDate),7) MonthNo, \n"
                   + "sum(isnull(qty,0)*isnull(aa.zgs,0)/3600) GS FROM  \n"
                   + "(SELECT ItemCode,CompleteDate,Qty,ZGS \n"
                   + "  FROM vie_ForeCastDataorderAnalysis WHERE CustNo \n"
                   + "IN (" + fileterFcCustNoSer + ")) aa  \n"
                   + "GROUP BY left(dbo.GetShortDate(aa.CompleteDate),7)";
            dtFCGS = accessdb.GetRecord(strSQL).ADODataRst;

            Cursor.Current = Cursors.WaitCursor;
            this.barTip.Caption = "进度:正在取得MO的起始月之前的工时数据...";
            Application.DoEvents();
            Cursor.Current = Cursors.WaitCursor;

            strSQL = "SELECT ISNULL(sum(GS),0) GS FROM vie_MOGSorderAnalysis  \n"
                        + " WHERE MonthNo<'" + startDate + "' \n";

            dtMoGSLast = accessdb.GetRecord(strSQL).ADODataRst;

            Cursor.Current = Cursors.WaitCursor;
            this.barTip.Caption = "进度:正在取得MO的工时数据...";
            Application.DoEvents();
            Cursor.Current = Cursors.WaitCursor;



            dtMoGS = accessdb.GetRecord(strSQL).ADODataRst;
        }


        //生成按区域分类的报表数据(GX OR DG)
        private bool CreateQYReport()
        {
            try
            {
                xApp = new Excel.ApplicationClass();
                Cursor currentCursor = Cursor.Current;

                //模板文件名称
                string sourceFileName = common.GetSolutionPath(Application.StartupPath) + @"orderAnalysis.xls";
                currentCursor = Cursor.Current;
                string startDate = dteFrom.Text;
                string endDate = dteTo.Text;


                //新建EXCEL文档
                string strFileName = common.ShowSaveFileDialog("保存Excel文件", "Microsoft Excel(*.xls)|*.xls");

                if (strFileName == "") return false;

                //取得客户的选择系列
                GetCustSer();

                //取得客户的选择系列
                GetCustSer();

                string curMonthDataT = CreateOrderGS();

                //显示数据
                ShowListData();


                //取得销货数据(取出最近一月有销货的-当月前两周的销货数据，客户不在forecast列表中的销货数据)

                //取得有料订单工时取erp中的MO数据，且物料最终交期为当月，并且小于或等于当前日期的Mo数据

                if (strFileName == "")
                {
                    Application.DoEvents();
                    return false;
                }

                Cursor.Current = Cursors.WaitCursor;
                this.barTip.Caption = "新建Excel文档...";
                Application.DoEvents();
                Cursor.Current = Cursors.WaitCursor;

                File.Copy(sourceFileName, strFileName, true);

                xApp.Workbooks.Open(strFileName, Missing.Value, Missing.Value,
                      Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                      Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                      Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                xApp.Visible = false;
                xApp.DisplayAlerts = false;
                xApp.Calculation = (Excel.XlCalculation)Excel.Constants.xlManual;
                Excel.Workbook xBook = xApp.Workbooks[1];
                Excel.Worksheet sheet = (Excel.Worksheet)xApp.ActiveSheet;// 获取当前工作表
                xBook = xApp.Workbooks[1];

                //按循环写入每个月的数据
                DateTime dteTmp = Convert.ToDateTime(this.dteFrom.Text + "-01");
                DateTime dteStartDate = Convert.ToDateTime(this.dteFrom.Text + "-01");
                DateTime dteEndDate = Convert.ToDateTime(this.dteTo.Text + "-01");

                Cursor.Current = Cursors.WaitCursor;
                this.barTip.Caption = "处理模板行...";
                Application.DoEvents();
                Cursor.Current = Cursors.WaitCursor;

                //复制月份数据
                int monthCount = (int)Microsoft.VisualBasic.DateAndTime.DateDiff(Microsoft.VisualBasic.DateInterval.Month, dteStartDate, dteEndDate) + 1;
                if (monthCount > 2)
                    InsertBlankRow(monthCount - 2, 4, xApp, sheet);


                //写入表头数据
                string strTitle = Convert.ToDateTime(dteFrom.Text + "-01").AddMonths(-1).Month.ToString();
                sheet.Cells[3, 4] = "无FC客户按" + strTitle + "月份实际订单工时计算";

                //写入每个月的数据
                for (int i = 0; i < monthCount; i++)
                {
                    //得到月份
                    string strMonthNo = dteTmp.ToString("yyyy-MM", System.Globalization.DateTimeFormatInfo.InvariantInfo);

                    //写入每个月的月份

                    sheet.Cells[4 + i * 2, 1] = dteTmp.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo);

                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "现有订单工时状态（H）";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;
                    string strLast = "";
                    if (i == 0)
                    {
                        strLast = "left(dbo.GetShortDate(RDate),7)<='" + strMonthNo + "'";
                    }
                    else
                        strLast = "left(dbo.GetShortDate(RDate),7)='" + strMonthNo + "'";

                    string sql = "SELECT  \n"
                               + " isnull(sum(qty*cb.Zgs),0)/3600 AS GS  \n"
                               + " FROM vie_ErpNotCompleteMoorderAnalysis aa  \n"
                               + "LEFT OUTER JOIN CapabilityBas cb  \n"
                               + "ON aa.ItemCode=cb.ItemCode COLLATE Chinese_PRC_CI_AS  \n"
                               + "WHERE " + strLast;
                    DataTable dtmoGSnew = accessdb.GetRecord(sql).ADODataRst;


                    sheet.Cells[4 + i * 2, 2] = Convert.ToDecimal(dtmoGSnew.Rows[0]["gs"]);



                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "FC工时（H）";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;



                    //写入FC工时（H）
                    if (i == 0)
                    {
                        if (WriteDataFlag)
                            sheet.Cells[4 + i * 2, 3] = Convert.ToDecimal(dtFCGSH1.Rows[0]["gs"]);
                        else if (this.dteFrom.Text == curMonthDataT)
                        {
                            sheet.Cells[4 + i * 2, 3] = 0;
                        }
                        else
                        {
                            DataRow[] drFCGS = dtFCGS.Select("MonthNo='" + strMonthNo + "'");
                            if (drFCGS.Length >= 1)
                            {

                                sheet.Cells[4 + i * 2, 3] = Convert.ToDecimal(drFCGS[0]["GS"]);
                            }
                        }

                    }
                    else
                    {
                        DataRow[] drFCGS = dtFCGS.Select("MonthNo='" + strMonthNo + "'");
                        if (drFCGS.Length >= 1)
                        {

                            sheet.Cells[4 + i * 2, 3] = Convert.ToDecimal(drFCGS[0]["GS"]);
                        }
                    }

                    //写入无FC客户按8月份实际订单工时计算
                    //(取出上一月的有销货数据-当月前两周的销货数据，通过料号关联BOM数据所在的部门。客户不在提供有forecast的列表中的所有MO数据)
                    string strLastMonthNo = dteTmp.AddMonths(-1).ToString("yyyy-MM", System.Globalization.DateTimeFormatInfo.InvariantInfo);
                    decimal lastMonthGSDG = 0;
                    decimal curMonthGSDG = 0;

                    decimal lastMonthGSGX = 0;
                    decimal curMonthGSGX = 0;

                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "无FC客户按8月份实际订单工时计算(DG)\n(取上月DG)";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;



                    string strSQL = "SELECT isnull(sum(qty*isnull(cb.ZGS,0))/3600,0) GS FROM vie_ErpSaleData aa \n"
                           + "LEFT OUTER JOIN CapabilityBas cb \n"
                           + "ON aa.PRD_NO=cb.ItemCode  COLLATE Chinese_PRC_CI_AS \n"
                           + "WHERE "
                           + "   LEFT(dbo.GetShortDate(aa.ps_dd),7)='" + strLastMonthNo + "' \n"
                           + " and   LEFT(dbo.GetShortDate(aa.OS_DD),7)='" + strLastMonthNo + "' \n"
                           + " AND isnull(aa.DEP,'') Not LIKE '%GX%' \n"
                           + " AND aa.SUP_PRD_NO NOT IN (" + fileterFcCustNoSer + ")";
                    dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                    if (dtOrderGS.Rows.Count >= 1)
                    {
                        curMonthGSDG = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]);
                        //写入数据(DG,第15列)
                        sheet.Cells[4 + i * 2, 15] = curMonthGSDG;
                    }





                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "无FC客户按8月份实际订单工时计算(GX)(取当月GX)";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;

                    //取当月份的数据(DG)
                    //strSQL = "SELECT isnull(sum(qty*isnull(cb.ZGS,0))/3600,0) GS FROM vie_ErpSaleData aa \n"
                    //        + "LEFT OUTER JOIN CapabilityBas cb \n"
                    //        + "ON aa.PRD_NO=cb.ItemCode  COLLATE Chinese_PRC_CI_AS\n"
                    //        + "WHERE dbo.GetShortDate(aa.ps_dd)<='" + strMonthNo + "-15' \n"
                    //        + " and   LEFT(dbo.GetShortDate(aa.ps_dd),7)='" + strMonthNo + "' \n"
                    //        + " and   LEFT(dbo.GetShortDate(aa.OS_DD),7)='" + strMonthNo + "' \n"
                    //        + "AND isnull(aa.DEP,'')  LIKE '%GX%' \n"
                    //        + "AND aa.SUP_PRD_NO NOT IN (" + fileterFcCustNoSer + ")";


                    strSQL = "SELECT isnull(sum(qty*isnull(cb.ZGS,0))/3600,0) GS FROM vie_ErpSaleData aa \n"
                            + "LEFT OUTER JOIN CapabilityBas cb \n"
                            + "ON aa.PRD_NO=cb.ItemCode  COLLATE Chinese_PRC_CI_AS\n"
                            + "WHERE  LEFT(dbo.GetShortDate(aa.ps_dd),7)='" + strLastMonthNo + "' \n"
                            + " and   LEFT(dbo.GetShortDate(aa.OS_DD),7)='" + strLastMonthNo + "' \n"
                            + "AND isnull(aa.DEP,'')  LIKE '%GX%' \n"
                            + "AND aa.SUP_PRD_NO NOT IN (" + fileterFcCustNoSer + ")";
                    dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                    if (dtOrderGS.Rows.Count >= 1)
                    {
                        curMonthGSGX = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]);
                        //写入数据(DG,第15列)
                        sheet.Cells[5 + i * 2, 15] = curMonthGSGX;
                    }



                    //写入合计（直接按模板公式)

                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "写入有料订单工时(DG)";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;

                    //写入有料订单工时(东莞)(取erp中的MO数据，且物料最终交期为小于或等于当月的25日的MO数据)
                    string strAdd = "";
                    if (i == 0)
                        strAdd = " and  left(dbo.GetShortDate(Rdate),7)<='" + strMonthNo + "'";
                    else
                        strAdd = " and  left(dbo.GetShortDate(Rdate),7)='" + strMonthNo + "'";

                    strSQL = "SELECT \n"
                           + " isnull(sum(qty*isnull(cb.ZGS,0))/3600,0) AS GS \n"
                           + " FROM vie_ErpNotCompleteMoorderAnalysis aa \n"
                           + "LEFT OUTER JOIN CapabilityBas cb \n"
                           + "ON aa.ItemCode=cb.ItemCode COLLATE Chinese_PRC_CI_AS \n"
                           + "WHERE dbo.GetShortDate(MrpDate)<='" + strMonthNo + "-25' \n"
                           + strAdd
                           + "  AND isnull(dep,'') NOT LIKE '%GX%' \n";

                    dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                    if (dtOrderGS.Rows.Count >= 1)
                        sheet.Cells[4 + i * 2, 10] = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]);
                    //写入有料订单工时(广西)(取erp中的MO数据，且物料最终交期为小于或等于当月的25日的MO数据)

                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "写入有料订单工时(GX)";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;
                    strSQL = "SELECT \n"
                            + " isnull(sum(qty*isnull(cb.zgs,0))/3600,0) AS GS \n"
                            + " FROM vie_ErpNotCompleteMoorderAnalysis aa \n"
                            + "LEFT OUTER JOIN CapabilityBas cb \n"
                            + "ON aa.ItemCode=cb.ItemCode COLLATE Chinese_PRC_CI_AS \n"
                            + "WHERE   dbo.GetShortDate(MrpDate)<='" + strMonthNo + "-25' "
                            + strAdd
                            + " AND isnull(dep,'')  LIKE '%GX%' \n";
                    dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                    if (dtOrderGS.Rows.Count >= 1)
                        sheet.Cells[5 + i * 2, 10] = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]);


                    //写入大客户走排期数据
                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "写入取当月的客户排期中存在的客户的Mo的数据(DG)";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;
                    string strFilterCur = "";
                    decimal balanceQty = 0;
                    //如果是第一个月，则需要加上当月之前的数据
                    if (i == 0)
                    {
                        strSQL = "SELECT  \n"
                      + "isnull(sum(qty*isnull(cb.ZGS,0))/3600,0) AS GS \n"
                      + " FROM vie_ErpNotCompleteMoorderAnalysis aa \n"
                      + "LEFT OUTER JOIN CapabilityBas cb \n"
                      + "ON aa.ItemCode=cb.ItemCode COLLATE Chinese_PRC_CI_AS \n"
                      + "WHERE LEFT( dbo.GetShortDate(RDate),7)<'" + strMonthNo + "' \n"
                      + " and CUS_NO IN (" + filterCustPQSer + ") \n"
                      + " AND isnull(dep,'') NOT LIKE '%GX%' \n";
                        dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                        if (dtOrderGS.Rows.Count >= 1)
                        {
                            balanceQty = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]);
                        }

                    }

                    //取当月的客户排期中存在的客户的Mo的数据
                    strSQL = "SELECT LEFT( dbo.GetShortDate(RDate),7) AS MonthNo, \n"
                       + "isnull(sum(qty*isnull(cb.ZGS,0))/3600,0) AS GS \n"
                       + " FROM vie_ErpNotCompleteMoorderAnalysis aa \n"
                       + "LEFT OUTER JOIN CapabilityBas cb \n"
                       + "ON aa.ItemCode=cb.ItemCode COLLATE Chinese_PRC_CI_AS \n"
                       + "WHERE LEFT( dbo.GetShortDate(RDate),7)='" + strMonthNo + "' \n"
                       + " and CUS_NO IN (" + filterCustPQSer + ") \n"
                       + " AND isnull(dep,'') NOT LIKE '%GX%' \n"
                       + " group by  LEFT( dbo.GetShortDate(RDate),7)";

                    dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                    if (dtOrderGS.Rows.Count >= 1)
                        sheet.Cells[4 + i * 2, 12] = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]) + balanceQty;


                    balanceQty = 0;
                    //如果是第一个月，则需要加上当月之前的数据
                    if (i == 0)
                    {
                        strSQL = "SELECT LEFT( dbo.GetShortDate(RDate),7) AS MonthNo, \n"
                          + "isnull(sum(qty*isnull(cb.ZGS,0))/3600,0) AS GS \n"
                          + " FROM vie_ErpNotCompleteMoorderAnalysis aa \n"
                          + "LEFT OUTER JOIN CapabilityBas cb \n"
                          + "ON aa.ItemCode=cb.ItemCode COLLATE Chinese_PRC_CI_AS \n"
                          + "WHERE LEFT( dbo.GetShortDate(RDate),7)<'" + strMonthNo + "' \n"
                          + " and CUS_NO IN (" + filterCustPQSer + ") \n"
                          + " AND isnull(dep,'')  LIKE '%GX%' \n"
                          + " group by  LEFT( dbo.GetShortDate(RDate),7)";
                        dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                        if (dtOrderGS.Rows.Count >= 1)
                        {
                            balanceQty = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]);
                        }

                    }

                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "写入取当月的客户排期中存在的客户的Mo的数据(GX)";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;
                    strSQL = "SELECT LEFT( dbo.GetShortDate(RDate),7) AS MonthNo, \n"
                          + "isnull(sum(qty*isnull(cb.ZGS,0))/3600,0) AS GS \n"
                          + " FROM vie_ErpNotCompleteMoorderAnalysis aa \n"
                          + "LEFT OUTER JOIN CapabilityBas cb \n"
                          + "ON aa.ItemCode=cb.ItemCode COLLATE Chinese_PRC_CI_AS \n"
                          + "WHERE LEFT( dbo.GetShortDate(RDate),7)='" + strMonthNo + "' \n"
                          + " and CUS_NO IN (" + filterCustPQSer + ") \n"
                          + " AND isnull(dep,'')  LIKE '%GX%' \n"
                          + " group by  LEFT( dbo.GetShortDate(RDate),7)";
                    dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                    if (dtOrderGS.Rows.Count >= 1)
                        sheet.Cells[5 + i * 2, 12] = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]) + balanceQty;


                    //写入本月生产正式单工时(本月的DG的Mo工时合计)
                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "本月生产正式单工时(本月的DG的Mo工时合计)(DG)";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;

                    //本月生产正式单工时(本月的DG的Mo工时合计)

                    if (i == 0)
                    {
                        strAdd = "WHERE left(dbo.GetShortDate(RDate),7)<='" + strMonthNo + "'";
                    }
                    else
                        strAdd = "WHERE left(dbo.GetShortDate(RDate),7)='" + strMonthNo + "'";
                    strSQL = "SELECT \n"
                           + " isnull(sum(qty*cb.Zgs)/3600,0) AS GS \n"
                           + " FROM vie_ErpNotCompleteMoorderAnalysis aa \n"
                           + "LEFT OUTER JOIN CapabilityBas cb \n"
                           + "ON aa.ItemCode=cb.ItemCode COLLATE Chinese_PRC_CI_AS \n"
                           + strAdd
                           + "  AND isnull(dep,'') NOT LIKE '%GX%' \n";

                    dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                    if (dtOrderGS.Rows.Count >= 1)
                        sheet.Cells[4 + i * 2, 13] = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]);

                    //写入当月FC的数据（14列)
                    Cursor.Current = Cursors.WaitCursor;
                    this.barTip.Caption = "写入(" + strMonthNo + ")" + "当月FC的数据(DG)";
                    Application.DoEvents();
                    Cursor.Current = Cursors.WaitCursor;

                    strSQL = "SELECT  \n"
                           + " isnull(sum(qty*Zgs)/3600,0) GS \n"
                           + "  FROM vie_ForeCastDataorderAnalysis aa \n"
                           + "  WHERE   dep NOT LIKE '%GX%' AND  \n"
                           + " dep NOT LIKE '%NLP%'   "
                           + "AND  LEFT(dbo.getshortdate(aa.CompleteDate),7)='" + strMonthNo + "' \n";
                    dtOrderGS = accessdb.GetRecord(strSQL).ADODataRst;
                    if (dtOrderGS.Rows.Count >= 1)
                        sheet.Cells[4 + i * 2, 14] = Convert.ToDecimal(dtOrderGS.Rows[0]["GS"]);


                    dteTmp = dteTmp.AddMonths(1);
                }

                //写入表头
                //sheet.Cells[1, 1] = strReportTilte;

                xApp.ScreenUpdating = true;
                xApp.Calculation = (Excel.XlCalculation)Excel.Constants.xlAutomatic;
                xBook.Save();
                xApp.Quit(); // 退出 Excel
                KillExcelProcess.Kill(xApp);//调用kill当前excel进程 
                xApp = null;
                if (DevExpress.XtraEditors.XtraMessageBox.Show("Excel文件已成功生成,现在打开文件吗(Y/N)?\n" + "文件信息:" + strFileName, common.sysParameter.MessageInf, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No) return false;

                //打开文件
                if (System.IO.File.Exists(strFileName))
                {
                    System.Diagnostics.Process process = new System.Diagnostics.Process();
                    process.StartInfo.FileName = strFileName;
                    process.StartInfo.Verb = "Open";
                    process.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal;
                    process.Start();
                }
                return true;
            }
            catch (Exception ex)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message, common.sysParameter.MessageInf, MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
            finally
            {
                if(xApp!=null)
                {
                    KillExcelProcess.Kill(xApp);  //调用kill当前excel进程 
                }
            }
         

        }

        private void GetCustSer()
        {
            //forecast客户的列表
            Cursor.Current = Cursors.WaitCursor;
            this.barTip.Caption = "进度:正在取得取得forecast客户的列表数据...";
            Application.DoEvents();
            Cursor.Current = Cursors.WaitCursor;

            //string fileterFcCustNoSer = "";
            //string FcCustNoSer = common.sysParameter.FcCustNoSer;

            string[] FcCustNoSerArray = FcCustNoSer.Split(',');
            if (FcCustNoSerArray.Length >= 1)
            {
                for (int i = 0; i < FcCustNoSerArray.Length; i++)
                {
                    fileterFcCustNoSer += "'" + FcCustNoSerArray[i] + "',";
                }

                fileterFcCustNoSer = fileterFcCustNoSer.Substring(0, fileterFcCustNoSer.Length - 1);
            }


            //取得客户排期的数据

            Cursor.Current = Cursors.WaitCursor;
            this.barTip.Caption = "进度:正在取得客户排期的数据...";
            Application.DoEvents();
            Cursor.Current = Cursors.WaitCursor;

            //string filterCustPQSer;
            //string CustPQSer = common.sysParameter.CustPQSer;

            string[] CustPQSerArray = CustPQSer.Split(',');
            filterCustPQSer = "";
            if (CustPQSerArray.Length >= 1)
            {
                for (int i = 0; i < CustPQSerArray.Length; i++)
                {
                    filterCustPQSer += "'" + CustPQSerArray[i] + "',";
                }
                filterCustPQSer = filterCustPQSer.Substring(0, filterCustPQSer.Length - 1);

            }



            //取得客户排期的数据
            Cursor.Current = Cursors.WaitCursor;
            this.barTip.Caption = "进度:正在取得计算FC工时的在手订单客户列表数据...";
            Application.DoEvents();
            Cursor.Current = Cursors.WaitCursor;

            //string filterCustMoSer;
            //string CustMoSer = common.sysParameter.FcCustNoMo;

            string[] CustMoSerArray = CustMoSer.Split(',');
            filterCustMoSer = "";
            if (CustMoSerArray.Length >= 1)
            {
                for (int i = 0; i < CustMoSerArray.Length; i++)
                {
                    filterCustMoSer += "'" + CustMoSerArray[i] + "',";
                }
                filterCustMoSer = filterCustMoSer.Substring(0, filterCustMoSer.Length - 1);

            }
        }

        //数据检查
        private bool CheckData()
        {
            if (dteFrom.Text == "")
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("开始月份不能为空!", common.sysParameter.MessageInf, MessageBoxButtons.OK, MessageBoxIcon.Information);
                dteFrom.Focus();
                return false;

            }

            if (dteTo.Text == "")
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("截止月份不能为空!", common.sysParameter.MessageInf, MessageBoxButtons.OK, MessageBoxIcon.Information);
                dteTo.Focus();
                return false;

            }

            if (this.dteFrom.Text.CompareTo(this.dteTo.Text) == 1)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("开始月份不能大于截止月份!", common.sysParameter.MessageInf, MessageBoxButtons.OK, MessageBoxIcon.Information);
                dteTo.Focus();
                return false;
            }

            return true;
        }

        private void InsertBlankRow(int rowCount, int startRow, Excel.Application xApp, Excel.Worksheet sheet)
        {
            Excel.Range range;

            for (int i = 1; i <= rowCount; i++)
            {
                range = (Excel.Range)xApp.Rows[(startRow).ToString() + ":" + (startRow + 1).ToString(), Missing.Value];
                range.Select();
                range.Copy(Missing.Value);
                range = sheet.get_Range("A" + (startRow + 2).ToString());
                range.Select();
                range.EntireRow.Insert(Missing.Value);
            }

        }

        private void ListView_CustomDrawRowIndicator(object sender, DevExpress.XtraGrid.Views.Grid.RowIndicatorCustomDrawEventArgs e)
        {
            e.Appearance.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Far;
            if (e.Info.IsRowIndicator)
            {
                if (e.RowHandle >= 0)
                {
                    e.Info.DisplayText = (e.RowHandle + 1).ToString();
                }
                else if (e.RowHandle < 0 && e.RowHandle > -1000)
                {
                    e.Info.Appearance.BackColor = System.Drawing.Color.AntiqueWhite;
                    e.Info.DisplayText = "组" + e.RowHandle.ToString();
                }
            }
        }

        private void ListViewFc_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            if (e.MenuType == DevExpress.XtraGrid.Views.Grid.GridMenuType.Column)
            {
                DevExpress.XtraGrid.Menu.GridViewColumnMenu menu = e.Menu as GridViewColumnMenu;
                if (menu.Column != null)
                {
                    //Adding new items 
                    DevExpress.XtraGrid.Views.Grid.GridView listView = sender as DevExpress.XtraGrid.Views.Grid.GridView;
                    menu.Column.Tag = listView.Tag;
                    menu.Items.Add(CreateCheckItem("导出EXcel", menu.Column, FixedStyle.None,
                      imgList.Images[0]));


                }

            }
        }

        //Create a menu item 
        DXMenuItem CreateCheckItem(string caption, GridColumn column, FixedStyle style, Image image)
        {
            DXMenuItem item = new DXMenuItem(caption, new EventHandler(OnExportClick), image);

            item.Tag = column.Tag;
            return item;
        }

        //Menu item click handler 
        void OnExportClick(object sender, EventArgs e)
        {
            DXMenuItem item = sender as DXMenuItem;
            DevExpress.XtraGrid.Views.Grid.GridView listView = new DevExpress.XtraGrid.Views.Grid.GridView();

            switch (item.Tag.ToString())
            {
                case "fc":
                    listView = ListViewFc;
                    break;
                case "mo":
                    listView = ListViewMo;
                    break;
               

            }
            if (!common.CheckExistsData(listView)) return;
            common.ExportExcel(listView);
            return;
        }

     

    }
}
